<?php
namespace app\admin\controller;
use think\Controller;
use think\Db;
use think\Request;
use think\Session;


class User extends Controller{
    public function __construct(){
        parent::__construct();
        if (!Session::has('admin')){
            $this->redirect('index/login');
        }

        $controller = strtolower(Request::instance()->controller());
        $action = strtolower(Request::instance()->action());
        $this->assign('action',"{$controller}/{$action}");
        $this->assign('active',$action);
    }
    //查出学生
 public function student(){
     if (request()->get()){
         if (Session::has('search')){
             $student=Db::table('student')->whereLike('name|apartment|stuid|grade|class','%'.Session::get('search').'%')->paginate(30);
         }else{
             if ((Session::get('apartment')!=='')){
                 $student=Db::table('student')->where('apartment',Session::get('apartment'))->paginate(30);
             }else{
                 $student=Db::table('student')->paginate(30);
             }

         }
            $this->assign('student',$student);
     }else if (request()->isPost()){
         Session::set('search',input('search'));
         if(Session::get('apartment')!==''){
             $student=Db::table('student')->where('apartment',Session::get('apartment'))->whereLike('name|apartment|stuid|grade|class','%'.input('search').'%')->paginate(30);
         }else{
             $student=Db::table('student')->whereLike('name|apartment|stuid|grade|class','%'.input('search').'%')->paginate(30);
         }
         $this->assign('student',$student);
     }else{
         Session::delete('search');
         if ((Session::get('apartment')!=='')){
             $student=Db::table('student')->where('apartment',Session::get('apartment'))->paginate(30);
             $this->assign('student',$student);
         }else{
             $student=Db::table('student')->paginate(30);
             $this->assign('student',$student);
         }

     }

     return view();
 }
    //查出教工
    public function teacher(){
        $teacher=Db::table('teacher')->paginate(30);
        $this->assign('teacher',$teacher);
        return view();
    }
    //查出校外
    public function outuser(){
        $outuser=Db::table('outuser')->paginate(30);
        $this->assign('outuser',$outuser);
        return view();
    }
    //删除学生
 public function deletestudent(){
     $id=input('get.id');
     Db::table('student')->where('id',$id)->delete();
     $this->redirect($_SERVER["HTTP_REFERER"]);
 }
    //删除教工
    public function deleteteacher(){
        $id=input('get.id');
        Db::table('teacher')->where('id',$id)->delete();
        $this->redirect($_SERVER["HTTP_REFERER"]);
    }
    //删除校外
   public function deleteoutuser(){
    $id=input('get.id');
    Db::table('outuser')->where('id',$id)->delete();
    $this->redirect($_SERVER["HTTP_REFERER"]);
}

    //导出学生
    public function excelstudent() {
     $data=Db::table('student')->select();
     vendor("PHPExcel.PHPExcel");
    $objPHPExcel = new \PHPExcel();
    // 设置sheet
    $objPHPExcel->setActiveSheetIndex(0);
    // 设置列的宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(30);
    // 设置表头
    $objPHPExcel->getActiveSheet()->SetCellValue('A1', '姓名');
    $objPHPExcel->getActiveSheet()->SetCellValue('B1', '学号');
    $objPHPExcel->getActiveSheet()->SetCellValue('C1', '联系电话');
    $objPHPExcel->getActiveSheet()->SetCellValue('D1', '邮箱');
    $objPHPExcel->getActiveSheet()->SetCellValue('E1', '年级');
    $objPHPExcel->getActiveSheet()->SetCellValue('F1', '系部');
    $objPHPExcel->getActiveSheet()->SetCellValue('G1', '班级');
    $objPHPExcel->getActiveSheet()->SetCellValue('H1', '注册时间');
    //存取数据
    $num = 2;
    foreach ($data as $k => $v) {
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . $num, $v['name']);
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $num, $v['stuid']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $num, $v['phone']);
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $num, $v['email']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . $num, $v['grade']);
         $objPHPExcel->getActiveSheet()->SetCellValue('F' . $num, $v['apartment']);
         $objPHPExcel->getActiveSheet()->SetCellValue('G' . $num, $v['class']);
        $objPHPExcel->getActiveSheet()->SetCellValue('H' . $num, $v['time']);
        $num++;
    }


      
    // 文件名称
    $fileName ='学生表';
    $xlsName = iconv('utf-8', 'gb2312', $fileName);
    // 设置工作表名
    $objPHPExcel->getActiveSheet()->setTitle('sheet');
    //下载 excel5与excel2007
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    ob_end_clean();     // 清除缓冲区,避免乱码
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl;charset=UTF-8");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header("Content-Disposition:attachment;filename=" . $xlsName . ".xls");
    header("Content-Transfer-Encoding:binary");
    $objWriter->save("php://output");
      
      /*  $tableName  = "学生表";
        $Cell  = array(
            array('name','姓名'),
            array('psw','登陆密码'),
            array('salt','密钥'),
            array('stuid','学号'),
            array('phone','联系电话'),
            array('email','邮箱'),
            array('grade','年级'),
            array('apartment','系部'),
            array('class','班级'),
            array('time','注册时间'),
        );
        $data=Db::table('student')->select();
        $this->PHPExcel($tableName,$Cell,$data); */
    }
    //导出教工
    public function excelteacher() {
     $data=Db::table('teacher')->select();
     vendor("PHPExcel.PHPExcel");
    $objPHPExcel = new \PHPExcel();
    // 设置sheet
    $objPHPExcel->setActiveSheetIndex(0);
    // 设置列的宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
      
    // 设置表头
    $objPHPExcel->getActiveSheet()->SetCellValue('A1', '姓名');
    $objPHPExcel->getActiveSheet()->SetCellValue('B1', '联系电话');
    $objPHPExcel->getActiveSheet()->SetCellValue('C1', '邮箱');
    $objPHPExcel->getActiveSheet()->SetCellValue('D1', '部门');
    $objPHPExcel->getActiveSheet()->SetCellValue('E1', '注册时间');

    //存取数据
    $num = 2;
    foreach ($data as $k => $v) {
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . $num, $v['name']);
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $num, $v['phone']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $num, $v['email']);
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $num, $v['apartment']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . $num, $v['time']);
        $num++;
    }

      
    // 文件名称
    $fileName ='教工表';
    $xlsName = iconv('utf-8', 'gb2312', $fileName);
    // 设置工作表名
    $objPHPExcel->getActiveSheet()->setTitle('sheet');
    //下载 excel5与excel2007
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    ob_end_clean();     // 清除缓冲区,避免乱码
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl;charset=UTF-8");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header("Content-Disposition:attachment;filename=" . $xlsName . ".xls");
    header("Content-Transfer-Encoding:binary");
    $objWriter->save("php://output");
      
      /*  $tableName  = "教工表";
        $Cell  = array(
            array('name','姓名'),
            array('psw','登陆密码'),
            array('salt','密钥'),
            array('phone','联系电话'),
            array('email','邮箱'),
            array('apartment','部门'),
            array('time','注册时间'),
        );
        $data=Db::table('teacher')->select();
        $this->PHPExcel($tableName,$Cell,$data); */
    }
    //导出校外
    public function exceloutuser() {
      $data=Db::table('outuser')->select();
     vendor("PHPExcel.PHPExcel");
    $objPHPExcel = new \PHPExcel();
    // 设置sheet
    $objPHPExcel->setActiveSheetIndex(0);
    // 设置列的宽度
    $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
    $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(30);
    $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(30);
      
    // 设置表头
    $objPHPExcel->getActiveSheet()->SetCellValue('A1', '姓名');
    $objPHPExcel->getActiveSheet()->SetCellValue('B1', '联系电话');
    $objPHPExcel->getActiveSheet()->SetCellValue('C1', '邮箱');
    $objPHPExcel->getActiveSheet()->SetCellValue('D1', '单位');
    $objPHPExcel->getActiveSheet()->SetCellValue('E1', '注册时间');

    //存取数据
    $num = 2;
    foreach ($data as $k => $v) {
        $objPHPExcel->getActiveSheet()->SetCellValue('A' . $num, $v['name']);
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $num, $v['phone']);
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $num, $v['email']);
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $num, $v['apartment']);
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . $num, $v['time']);
        $num++;
    }


    // 文件名称
    $fileName ='校外表';
    $xlsName = iconv('utf-8', 'gb2312', $fileName);
    // 设置工作表名
    $objPHPExcel->getActiveSheet()->setTitle('sheet');
    //下载 excel5与excel2007
    $objWriter = new \PHPExcel_Writer_Excel5($objPHPExcel);
    ob_end_clean();     // 清除缓冲区,避免乱码
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl;charset=UTF-8");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");
    header("Content-Disposition:attachment;filename=" . $xlsName . ".xls");
    header("Content-Transfer-Encoding:binary");
    $objWriter->save("php://output");
      
     /*   $tableName  = "";
        $Cell  = array(
            array('name','姓名'),
            array('phone','联系电话'),
            array('email','邮箱'),
            array('apartment','单位'),
            array('time','注册时间'),
        );
        $data=Db::table('outuser')->select();
        $this->PHPExcel($tableName,$Cell,$data); */
    }
//导入学生
//    public function putstudent(){
//        $file = request()->file('file');
//
//        if ($file){
//            // 移动到框架应用根目录/uploads/ 目录下
//            $info = $file->validate(array('ext'=>'xlsx,xls'))->move( '../public','');
//            if (!$info){
//                $this->success('只允许xlsx,xls文件');
//            }
//            $file_name='../public/'.$info->getSaveName();
//            vendor("PHPExcel.PHPExcel");
//            vendor("PHPExcel.PHPExcel.PHPExcel");
//            vendor("PHPExcel.PHPExcel.Writer.Excel5");
//            vendor("PHPExcel.PHPExcel.Writer.Excel2007");
//            vendor("PHPExcel.PHPExcel.IOFactory");
//            $PHPExcel = new \PHPExcel();
//            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表 格后缀格式
//            if ($extension == 'xlsx') {
//                $objReader =new \PHPExcel_Reader_Excel2007();
//                $PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
//            }else if($extension == 'xls'){
//                $objReader = new \PHPExcel_Reader_Excel5();
//                $PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
//            }
//
//            $sheet = $PHPExcel->getSheet(0);
//            $highestRow = $sheet->getHighestRow(); // 取得总行数
//            for ($i = 2; $i <= $highestRow; $i++) {
//                $data['name'] = $PHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
//                $data['psw'] = $PHPExcel->getActiveSheet()->getCell("B" . $i)->getValue();
//                $data['salt'] = $PHPExcel->getActiveSheet()->getCell("C" . $i)->getValue();
//                $data['stuid'] = $PHPExcel->getActiveSheet()->getCell("D" . $i)->getValue();
//                $data['phone'] = $PHPExcel->getActiveSheet()->getCell("E" . $i)->getValue();
//                $data['email'] = $PHPExcel->getActiveSheet()->getCell("F" . $i)->getValue();
//                $data['grade'] = $PHPExcel->getActiveSheet()->getCell("G" . $i)->getValue();
//                $data['apartment'] = $PHPExcel->getActiveSheet()->getCell("H" . $i)->getValue();
//                $data['class'] = $PHPExcel->getActiveSheet()->getCell("I" . $i)->getValue();
//                $data['time'] = $PHPExcel->getActiveSheet()->getCell("J" . $i)->getValue();
//                Db::table('student')->insert($data);
//            }
//            $this->success('上传成功');
//        }else{
//            $this->error('没有文件上传');
//        }
//    }
//导入教工
//    public function putteacher(){
//        $file = request()->file('file');
//
//        if ($file){
//            // 移动到框架应用根目录/uploads/ 目录下
//            $info = $file->validate(array('ext'=>'xlsx,xls'))->move( '../public','');
//            if (!$info){
//                $this->success('只允许xlsx,xls文件');
//            }
//            $file_name='../public/'.$info->getSaveName();
//            vendor("PHPExcel.PHPExcel");
//            vendor("PHPExcel.PHPExcel.PHPExcel");
//            vendor("PHPExcel.PHPExcel.Writer.Excel5");
//            vendor("PHPExcel.PHPExcel.Writer.Excel2007");
//            vendor("PHPExcel.PHPExcel.IOFactory");
//            $PHPExcel = new \PHPExcel();
//            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表 格后缀格式
//            if ($extension == 'xlsx') {
//                $objReader =new \PHPExcel_Reader_Excel2007();
//                $PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
//            }else if($extension == 'xls'){
//                $objReader = new \PHPExcel_Reader_Excel5();
//                $PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
//            }
//
//            $sheet = $PHPExcel->getSheet(0);
//            $highestRow = $sheet->getHighestRow(); // 取得总行数
//            for ($i = 2; $i <= $highestRow; $i++) {
//                $data['name'] = $PHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
//                $data['psw'] = $PHPExcel->getActiveSheet()->getCell("B" . $i)->getValue();
//                $data['salt'] = $PHPExcel->getActiveSheet()->getCell("C" . $i)->getValue();
//                $data['phone'] = $PHPExcel->getActiveSheet()->getCell("E" . $i)->getValue();
//                $data['email'] = $PHPExcel->getActiveSheet()->getCell("F" . $i)->getValue();
//                $data['apartment'] = $PHPExcel->getActiveSheet()->getCell("G" . $i)->getValue();
//                $data['time'] = $PHPExcel->getActiveSheet()->getCell("H" . $i)->getValue();
//                Db::table('teacher')->insert($data);
//            }
//            $this->success('上传成功');
//        }else{
//            $this->error('没有文件上传');
//        }
//    }
//导入校外
//    public function putoutuser(){
//        $file = request()->file('file');
//
//        if ($file){
//            // 移动到框架应用根目录/uploads/ 目录下
//            $info = $file->validate(array('ext'=>'xlsx,xls'))->move( '../public','');
//            if (!$info){
//                $this->success('只允许xlsx,xls文件');
//            }
//            $file_name='../public/'.$info->getSaveName();
//            vendor("PHPExcel.PHPExcel");
//            vendor("PHPExcel.PHPExcel.PHPExcel");
//            vendor("PHPExcel.PHPExcel.Writer.Excel5");
//            vendor("PHPExcel.PHPExcel.Writer.Excel2007");
//            vendor("PHPExcel.PHPExcel.IOFactory");
//            $PHPExcel = new \PHPExcel();
//            $extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表 格后缀格式
//            if ($extension == 'xlsx') {
//                $objReader =new \PHPExcel_Reader_Excel2007();
//                $PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
//            }else if($extension == 'xls'){
//                $objReader = new \PHPExcel_Reader_Excel5();
//                $PHPExcel = $objReader->load($file_name, $encode = 'utf-8');
//            }
//
//            $sheet = $PHPExcel->getSheet(0);
//            $highestRow = $sheet->getHighestRow(); // 取得总行数
//            for ($i = 2; $i <= $highestRow; $i++) {
//                $data['name'] = $PHPExcel->getActiveSheet()->getCell("A" . $i)->getValue();
//                $data['psw'] = $PHPExcel->getActiveSheet()->getCell("B" . $i)->getValue();
//                $data['salt'] = $PHPExcel->getActiveSheet()->getCell("C" . $i)->getValue();
//                $data['phone'] = $PHPExcel->getActiveSheet()->getCell("E" . $i)->getValue();
//                $data['email'] = $PHPExcel->getActiveSheet()->getCell("F" . $i)->getValue();
//                $data['apartment'] = $PHPExcel->getActiveSheet()->getCell("G" . $i)->getValue();
//                $data['time'] = $PHPExcel->getActiveSheet()->getCell("H" . $i)->getValue();
//                Db::table('outuser')->insert($data);
//            }
//            $this->success('上传成功');
//        }else{
//            $this->error('没有文件上传');
//        }
//    }
}